package com.github.shiqiyue.app.api.sapi.controller.dbms;

import com.github.shiqiyue.app.api.sapi.common.SApiBaseController;
import com.github.shiqiyue.app.api.sapi.controller.dbms.dto.DataQueryDTO;
import com.github.shiqiyue.app.api.sapi.controller.dbms.dto.TableInfoDTO;
import com.github.shiqiyue.app.common.result.Result;
import com.github.shiqiyue.app.modules.dbms.service.IDatabaseService;
import com.github.shiqiyue.app.modules.dbms.service.IDbQueryService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.*;

/***
 * 数据库-查询
 * @author wenyao.wu
 * @date 2019/8/9
 */
@Slf4j
@RestController
@RequestMapping("/sapi/dbms/database/query")
public class ApiDatabaseQueryController extends SApiBaseController {

    @Autowired
    private IDatabaseService databaseService;

    @Autowired
    private IDbQueryService dbQueryService;

    @ExceptionHandler(BadSqlGrammarException.class)
    public Result badSqlGrammarException(BadSqlGrammarException exception) {
        log.error("SQL语法错误", exception);
        return Result.fail(exception.getMessage());
    }

    /***
     * 获取表信息
     * @param databaseId
     * @return
     */
    @PostMapping("tables")
    public Result getTables(Long databaseId) {
        List<TableInfoDTO> tableInfoList = dbQueryService.getTablelInfoByDatabaseId(databaseId);
        if (CollectionUtils.isNotEmpty(tableInfoList)) {
            Collections.sort(tableInfoList, new Comparator<TableInfoDTO>() {
                @Override
                public int compare(TableInfoDTO o1, TableInfoDTO o2) {
                    return o1.getTableName().compareTo(o2.getTableName());
                }
            });
        }
        return Result.success(tableInfoList);
    }

    /***
     * 执行sql
     * @param databaseId
     * @param sql
     * @return
     */
    @PostMapping("execute")
    public Result executeSql(Long databaseId, String sql) {
        if (!sql.trim().toLowerCase().startsWith("select")) {
            return Result.fail("仅支持select语句");
        }
        DataQueryDTO dataQueryDTO = dbQueryService.executeSelect(this.getCurrentUser().getId(), databaseId, sql);
        List<Map<String, Object>> datas = dataQueryDTO.getData();
        if (CollectionUtils.isNotEmpty(datas)) {
            for (Map<String, Object> data : datas) {
                for (String key : data.keySet()) {
                    if (data.get(key) instanceof Date) {
                        data.put(key, DateFormatUtils.format((Date) data.get(key), "yyyy-MM-dd HH:mm:ss"));
                    }
                }
            }
        }
        return Result.success(dataQueryDTO);
    }

    /***
     * 分析sql
     * @param databaseId
     * @param sql
     * @return
     */
    @PostMapping("analysis")
    public Result analysisSql(Long databaseId, String sql) {
        if (!sql.trim().toLowerCase().startsWith("select")) {
            return Result.fail("仅支持select语句");
        }
        DataQueryDTO dataQueryDTO = dbQueryService.analysisSql(this.getCurrentUser().getId(), databaseId, sql);
        List<Map<String, Object>> datas = dataQueryDTO.getData();
        if (CollectionUtils.isNotEmpty(datas)) {
            for (Map<String, Object> data : datas) {
                for (String key : data.keySet()) {
                    if (data.get(key) instanceof Date) {
                        data.put(key, DateFormatUtils.format((Date) data.get(key), "yyyy-MM-dd HH:mm:ss"));
                    }
                }
            }
        }
        return Result.success(dataQueryDTO);
    }


}
